# import requests
# json_text = requests.get('https://solr-aws-elb-production.ratemyprofessors.com//solr/rmp/select/?solrformat=true&rows=20&wt=json&json.wrf=noCB&callback=noCB&q=*%3A*+AND+schoolid_s%3A431&defType=edismax&qf=teacherfirstname_t%5E2000+teacherlastname_t%5E2000+teacherfullname_t%5E2000+autosuggest&bf=pow(total_number_of_ratings_i%2C2.1)&sort=total_number_of_ratings_i+desc&siteName=rmp&rows=20&start=0&fl=pk_id+teacherfirstname_t+teacherlastname_t+total_number_of_ratings_i+averageratingscore_rf+schoolid_s&fq=')
#
import pymysql
import re

conn = pymysql.connect(host='gsy-cloud', user='root', password='rootQWE', database='python', port=32769)
cursor = conn.cursor()
query_sql = "select comment from tb_rate_comment"
query_sql = """SELECT  tb.pro_pk_id,tb.rate_num,tb.pro_first_name,tb.pro_last_name,tavg.difficulty,tb.avg_rate,tb.department_name  FROM tb_teacher tb ,
(
	SELECT  avg(CAST(tr.difficulty as DECIMAL)) as difficulty,pro_pk_id FROM tb_rate_comment tr WHERE tr.pro_pk_id in 
		(
			SELECT tea.pro_pk_id FROM tb_teacher tea WHERE tea.pro_first_name like '%%%s'
		)

	GROUP BY pro_pk_id
) as tavg
where tb.pro_pk_id = tavg.pro_pk_id;"""
ll = ["Jacek Dzikowski",
      "Virgil Bistriceanu",
      "Yousef M.Elmehdwi",
      "Gruia Calinescu",
      "Michael K.Lee",
      "Ola Tannous",
      "Yousef M.Elmehdwi",
      "Yan Yan",
      "Dennis J. Hood",
      "Dusan Veselinovic",
      "Alexander J. Flueck",
      "Joohee Kim",
      "Geoffrey Williamson",
      "James Papademas",
      "Subhashish Ghosh",
      "Scott Akin Spyrison",
      "Brian R. Vanderjack",
      ]
for i in ll:
    tmepFirstName = i.split(" ")[0]
    cursor.execute(query_sql % tmepFirstName)
    a = cursor.fetchall()
    for row in a:
        print(row)
    print('-----------------------------------------------------------------------')